package database;
import po.*;

import java.sql.*;
import java.util.*;

import javax.swing.JOptionPane;

public class UserService {
	private String username;
	private String password;
	private Connection conn;
	private String ConnectURL="jdbc:mysql://127.0.0.1:3306";
	private String Database="/obss";
	
	public UserService(String username, String password) throws SQLException,ClassNotFoundException{
		this.username = username;
		this.password = password;
		Class.forName("com.mysql.jdbc.Driver");	

		conn=DriverManager.getConnection(ConnectURL+Database,username,password);
	}
	
	public String getDatabase(){return Database;}	
	public String getConnectURL(){return ConnectURL;}	
	public void setDatabase(String database){Database=database;}	
	public void setConnectURL(String URL){ConnectURL=URL;}	
	
	public void addUser(UserPO user) throws SQLException{
		
		Statement stat=conn.createStatement();
		String username,password,name;
		UserRole ROLE;
		
		
		username=user.getUsername();
		password=user.getPassword();
		name=user.getName();
		ROLE=user.getRole();
		
		
		String role = null;
		
		switch(ROLE){
		case CUSTOMER: 
			role="CUSTOMER";
			break;
		case SALES_MANAGER: 
			role="SALES_MANAGER";
			break;
		case MANAGER:
			role="MANAGER";
			break;
		case ADMINISTRATOR:
			role="ADMINISTRATOR";
			break;
		}
		
		String cmd=null;
		cmd="insert into userinfo values('"+username+"','"+password+"','"+name+"','"+role+"')";			
		stat.execute(cmd);
		
		if(role=="CUSTOMER"){
			String address=user.getCustomerPO().getAddress();
			String phone=user.getCustomerPO().getPhone();
			int score=user.getCustomerPO().getScore();
			int offsetTicket=user.getCustomerPO().getOffsetTicket();
			int discountTicket=user.getCustomerPO().getDiscountTicket();
							
			Statement stmt2=conn.createStatement();
			String cmd2=null;
			cmd2="insert into customerinfo values('"+name+"','"+address+"','"+phone+"',"+score+","+offsetTicket+","+discountTicket+",'"+username+"')";
			stmt2.execute(cmd2);
			
			stmt2.close();		
		}
		
		stat.close();
		
		}
	
	
	public void updateUser(UserPO user)throws SQLException{
		
			Statement stat=conn.createStatement();
			String username,password,name;
			UserRole ROLE;
			
			username=user.getUsername();
			password=user.getPassword();
			name=user.getName();
			ROLE=user.getRole();

			
			String role = null;
			
			switch(ROLE){
			case CUSTOMER: 
				role="CUSTOMER";
				break;
			case SALES_MANAGER: 
				role="SALES_MANAGER";
				break;
			case MANAGER:
				role="MANAGER";
				break;
			case ADMINISTRATOR:
				role="ADMINISTRATOR";
				break;
			}
			
			String cmd=null;
			cmd="update userinfo set username='"+username+"',password='"+password+"',name='"+name+"',UserRole='"+role+"' where username='"+username+"'";
			stat.execute(cmd);
			
			if(role=="CUSTOMER"){
			
				String custname=user.getCustomerPO().getName();
				String address=user.getCustomerPO().getAddress();
				String phone=user.getCustomerPO().getPhone();
				int score=user.getCustomerPO().getScore();
				int offsetTicket=user.getCustomerPO().getOffsetTicket();
				int discountTicket=user.getCustomerPO().getDiscountTicket();
								
				Statement stmt2=conn.createStatement();
				String cmd2=null;
				cmd2="update customerinfo set name='"+custname+"',address='"+address+"',phone='"+phone+"',score="+score+",offsetTicket="+offsetTicket+",discountTicket="+discountTicket+",username='"+username+"' where username='"+username+"'";
				stmt2.execute(cmd2);	
						
				stmt2.close();
			}

			stat.close();
			
			
		
		
	}
	
	public void deleteUser(UserPO user)throws SQLException{
		
			Statement stat=conn.createStatement();
			String username,password;
			UserRole ROLE;
			
			username=user.getUsername();
			password=user.getPassword();
			
			ROLE=user.getRole();
			String role = null;
			
			switch(ROLE){
			case CUSTOMER: 
				role="CUSTOMER";
				break;
			case SALES_MANAGER: 
				role="SALES_MANAGER";
				break;
			case MANAGER:
				role="MANAGER";
				break;
			case ADMINISTRATOR:
				role="ADMINISTRATOR";
				break;
			}
			
			String cmd=null;
			cmd="delete from userinfo where username='"+username+"' and password='"+password+"'";
			stat.execute(cmd);
			
			if(role=="CUSTOMER"){
				Statement stat2=conn.createStatement();
				String cmd2=null;
				cmd2="delete from customerinfo where username='"+username+"'";
				stat2.execute(cmd2);				
			
				stat2.close();
			}	
			
			
			stat.close();
			
			
	}
	
	public UserPO findUser(String username) throws SQLException{
			UserPO reUserPO=null;
			Statement stmt=conn.createStatement();
			String USERNAME=null,password=null,name=null,userrole=null;
						
			ResultSet rs=stmt.executeQuery("select *from userinfo where username='"+username+"'");
			
			
			while(rs.next()){
				USERNAME=rs.getString(1);
				password=rs.getString(2);
				name=rs.getString(3);
				userrole=rs.getString(4);
			}
			
			if(USERNAME==null){
				stmt.close();
				rs.close();
				return null;
			}
			
			else{
			
				UserRole ur=null;
				CustomerPO csmpo=null;
			
				switch(userrole){
				case "CUSTOMER":
					ur=UserRole.CUSTOMER;
					break;
				case "SALES_MANAGER":
					ur=UserRole.SALES_MANAGER;
					break;
				case "MANAGER":
					ur=UserRole.MANAGER;
					break;
				case "ADMINISTRATOR":
					ur=UserRole.ADMINISTRATOR;
					break;					
				}
				
			
				if(ur==UserRole.CUSTOMER){
					Statement stmt2=conn.createStatement();
					ResultSet rs2=stmt2.executeQuery("select *from customerinfo where username='"+username+"'");
						
					String address=null,phone=null;
					int score=0;
					int offsetTicket=0;
					int discountTicket=0;
					while(rs2.next()){
						address=rs2.getString(2);
						phone=rs2.getString(3);
						score=rs2.getInt(4);
						offsetTicket=rs2.getInt(5);
						discountTicket=rs2.getInt(6);
					}
				
					csmpo=new CustomerPO(name,address,phone,score,offsetTicket,discountTicket);
				}
			
				reUserPO=new UserPO(username,password,name,ur,csmpo);
			}
			stmt.close();
			rs.close();
			
			return reUserPO;
		
	}
	
	public ArrayList<UserPO> findUser(int low, int high) throws SQLException{
		ArrayList<UserPO> userList = new ArrayList<UserPO>();
		
		Statement stmt=conn.createStatement();
		String username = null;
					
		ResultSet rs=stmt.executeQuery("select * from customerinfo where score>="+low+" and score<"+high);
		
		
		while(rs.next()){
			username = rs.getString(7);
			
			UserPO user = this.findUser(username);
			userList.add(user);
		}
		
		stmt.close();
		rs.close();
		
		return userList;
	}
	
	public ArrayList<UserPO> findUser(int quota) throws SQLException{
		ArrayList<UserPO> userList = new ArrayList<UserPO>();
		
		Statement stmt=conn.createStatement();
		String username = null;
					
		ResultSet rs=stmt.executeQuery("select * from customerinfo where score>="+quota);
		
		
		while(rs.next()){
			username = rs.getString(7);
			
			UserPO user = this.findUser(username);
			userList.add(user);
		}
		
		stmt.close();
		rs.close();
		
		return userList;
	}
}
